package ext.mango.ab.po;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.UUID;

import javax.servlet.ServletOutputStream;

import mango.Mango;
import mango.functions.FuncDate;
import mango.functions.FuncString;
import mango.orm.DaoFactory;
import mango.orm.DBSession;
import mango.orm.DBSessionFactory;
import mango.orm.entity.Entity;
import ext.mango.ab.eo.EOABHouseKeeperDetail;
import ext.mango.ab.eo.EOABHouseKeeperTypeItems;
import ext.mango.base.Messages;
import ext.mango.base.POBase;

public class HouseKeeperDetail extends POBase{
    public boolean r(){
//        String sql= "select actionMonth,id,pid,spendtype||'-'||spendname spendtype,subspendname,money,actiondate from ABHouseKeeperDetail where actionMonth=? and operator=?";
        String sql= "select actionMonth,id,pid,concat(spendtype,'-',spendname) spendtype,subspendname,money, actiondate, date_format(actiondate, '%Y-%m-%d') actiondateStr from ABHouseKeeperDetail where actionMonth=? and operator=? order by spendtype"; //$NON-NLS-1$
        String month = this.getRequestParameter("ACTIONMONTH"); //$NON-NLS-1$
        if(FuncString.isBlank(month)) {
            month = FuncDate.getCurrentYearMonth();
        }
        List<Entity> list = DaoFactory.c(sql, EOABHouseKeeperDetail.class).setParameter(month, this.getCurrentUser().getId()).listQuery();
        this.setResult(Entity.toJSON(list, "housekeeperdetail")); //$NON-NLS-1$
        return true;
    }
    
    public boolean rchart() {
        String groupField = this.getRequestParameter("GROUPFIELD"); //$NON-NLS-1$
        String actionMonth = this.getRequestParameter("ACTIONMONTH"); //$NON-NLS-1$
        List<Entity> ret = new ArrayList<Entity>();
        String sql = ""; //$NON-NLS-1$
//        if("SUBSPENDNAME".equals(groupField)) {
//            sql = "SELECT 1 len, s/am rate, cnt, display, display||':￥'||s||',数量:'||cnt||',占比:'||round(s/am,2) * 100||'%' tip FROM("
//                +    "SELECT (SELECT SUM(money) FROM Abhousekeeperdetail WHERE actionmonth=? and operator=?) am,s,cnt,display FROM("
//                +       "SELECT SUM(money) s,COUNT(1) cnt,SUBSPENDNAME display FROM Abhousekeeperdetail WHERE actionmonth=? and operator=? GROUP BY SUBSPENDNAME"
//                +     ") tmp1"
//                +  ") tmp2";
//        }
//        else if("ACTIONDATE".equals(groupField)) {
//            sql = "SELECT 1 len, s/am rate, cnt, display, display||':￥'||s||',数量:'||cnt||',占比:'||round(s/am,2) * 100||'%' tip FROM("
//                +    "SELECT (SELECT SUM(money) FROM Abhousekeeperdetail WHERE actionmonth=? and operator=?) am,s,cnt,display FROM("
//                +       "SELECT SUM(money) s,COUNT(1) cnt,to_char(actiondate,'yyyy-mm-dd') display FROM Abhousekeeperdetail WHERE actionmonth=? and operator=? GROUP BY ACTIONDATE"
//                +     ") tmp1"
//                +  ") tmp2";
//        }
//        else {
//            sql = "SELECT 1 len, s/am rate, cnt, display, display||':￥'||s||',数量:'||cnt||',占比:'||round(s/am,2) * 100||'%' tip FROM("
//                +    "SELECT (SELECT SUM(money) FROM Abhousekeeperdetail WHERE actionmonth=? and operator=?) am,s,cnt,display FROM("
//                +       "SELECT SUM(money) s,COUNT(1) cnt,spendtype||'-'||spendname display FROM Abhousekeeperdetail WHERE actionmonth=? and operator=? GROUP BY spendtype,spendname"
//                +     ") tmp1"
//                +  ") tmp2";
//        }
        if("SUBSPENDNAME".equals(groupField)) { //$NON-NLS-1$
            sql = "SELECT 1 len, round(s/am,4)*100 rate, cnt, display, round(s,1) money FROM(" //$NON-NLS-1$
                +    "SELECT (SELECT SUM(money) FROM Abhousekeeperdetail WHERE actionmonth=? and operator=?) am,s,cnt,display FROM(" //$NON-NLS-1$
                +       "SELECT SUM(money) s,COUNT(1) cnt,SUBSPENDNAME display FROM Abhousekeeperdetail WHERE actionmonth=? and operator=? GROUP BY SUBSPENDNAME" //$NON-NLS-1$
                +     ") tmp1" //$NON-NLS-1$
                +  ") tmp2"; //$NON-NLS-1$
        }
        else if("ACTIONDATESTR".equals(groupField)) { //$NON-NLS-1$
            sql = "SELECT 1 len, round(s/am,4)*100 rate, cnt, display, round(s,1) money FROM(" //$NON-NLS-1$
                +    "SELECT (SELECT SUM(money) FROM Abhousekeeperdetail WHERE actionmonth=? and operator=?) am,s,cnt,display FROM(" //$NON-NLS-1$
                +       "SELECT SUM(money) s,COUNT(1) cnt,date_format(actiondate,'%Y-%m-%d') display FROM Abhousekeeperdetail WHERE actionmonth=? and operator=? GROUP BY ACTIONDATE" //$NON-NLS-1$
                +     ") tmp1" //$NON-NLS-1$
                +  ") tmp2"; //$NON-NLS-1$
        }
        else {
            sql = "SELECT 1 len, round(s/am,4)*100 rate, cnt, display, round(s,1) money FROM(" //$NON-NLS-1$
                +    "SELECT (SELECT SUM(money) FROM Abhousekeeperdetail WHERE actionmonth=? and operator=?) am,s,cnt,display FROM(" //$NON-NLS-1$
                +       "SELECT SUM(money) s,COUNT(1) cnt,concat(spendtype,'-',spendname) display FROM Abhousekeeperdetail WHERE actionmonth=? and operator=? GROUP BY spendtype,spendname" //$NON-NLS-1$
                +     ") tmp1" //$NON-NLS-1$
                +  ") tmp2"; //$NON-NLS-1$
        }
        ret = DaoFactory.c(sql).setParameter(actionMonth, this.getCurrentUser().getId(), actionMonth, this.getCurrentUser().getId()).listQuery();
        if (ret.size() ==0) {
            Entity e = new Entity().set("rate", "1").set("display", Messages.getString(this.getLanguage(), "nodata")).set("tip", Messages.getString(this.getLanguage(), "nodata")).set("len", 1); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ //$NON-NLS-4$ //$NON-NLS-5$ //$NON-NLS-6$ //$NON-NLS-7$
            ret.add(e);
        }
        this.setResult(Entity.toJSON(ret, "housekeeperdetail")); //$NON-NLS-1$
        return true;
    }
    
    public boolean readSpendType(){
//        String sql = "select code||'-'||codename spendtype from basecode where codetype='spendtype' order by code";
        String sql = "select concat(spendtype,'-',name) id,concat(spendtype,'-',name) text from ABHouseKeeperSpendType where operator=? or operator='default' order by spendtype"; //$NON-NLS-1$
        List<Entity> list = DaoFactory.c(sql).setParameter(this.getCurrentUser().getId()).listQuery();
        this.setResult(Entity.toJSON(Entity.JSON_FIELD_TYPE_LOWEER, list, "spendtype")); //$NON-NLS-1$
        return true;
    }
    
    public boolean updateSpendType(){
        String spendType = this.getRequestParameter("SPENDTYPE"); //$NON-NLS-1$
        if(FuncString.isBlank(spendType)){
            return false;
        }
        int idx = spendType.indexOf("-"); //$NON-NLS-1$
        if(idx <= 0){
            return false;
        }
        String code = spendType.substring(0, idx);
        String codeName = spendType.substring(idx + 1);
        
        String subSpendName = this.getRequestParameter("SUBSPENDNAME"); //$NON-NLS-1$
        EOABHouseKeeperTypeItems item = new EOABHouseKeeperTypeItems().setItem(subSpendName)
                .setSpendType(code).setOperator(this.getCurrentUser().getId());
//        String id = this.getRequestParameter("ID"); //$NON-NLS-1$
//        String sql = "update ABHouseKeeperDetail set spendType=?, spendName=? where id=?"; //$NON-NLS-1$
        DBSession session = DBSessionFactory.getSession();
        String sqldel = "delete from ABHouseKeeperTypeItems where item=? and operator=?";
        String sqlupdate = "update ABHouseKeeperDetail set spendType=?, spendName=? where subSpendName=? and operator=?";
        session.execute(sqldel, subSpendName, this.getCurrentUser().getId());
        session.save(item);
//        session.execute(sql, code, codeName, id);
        session.execute(sqlupdate, code, codeName, subSpendName, this.getCurrentUser().getId());
        session.commit();
        return true;
    }
    
    public boolean statistic(){
        String type = this.getRequestParameter("TYPE"); //$NON-NLS-1$
        String year = this.getRequestParameter("YEAR"); //$NON-NLS-1$
        String sql = null;
        if ("MONTH".equals(type)) { //$NON-NLS-1$
            sql = "select STATISTICKEY, money, -1 type from(" //$NON-NLS-1$
                +     "select actionMonth STATISTICKEY, sum(money) money from abhousekeeperdetail where operator=@operator and left(actionmonth, 4)=@year group by actionMonth" //$NON-NLS-1$
                +  ") t1"; //$NON-NLS-1$
        }
        else if ("SPENDTYPE".equals(type)) { //$NON-NLS-1$
            sql = "select STATISTICKEY, money, -1 type from(" //$NON-NLS-1$
                +     "select spendname STATISTICKEY, sum(money) money from abhousekeeperdetail where operator=@operator and left(actionmonth, 4)=@year group by spendname" //$NON-NLS-1$
                +  ") t1"; //$NON-NLS-1$
        }
        else if(type.startsWith("MONTHSPLIT")) {
            String[] param = type.split(":");
            String[] stype = null;
            if(param.length > 1) {
                stype = new String[param.length - 1];
                System.arraycopy(param, 1, stype, 0, stype.length);
                for(int i = 0; i < stype.length; i++) {
                    stype[i] = "'" + stype[i] + "'";
                }
            }
            sql = "select STATISTICKEY,STATISTICKEY month, sum(money) money, type from ("
                + " select "
                + " actionMonth STATISTICKEY, "
                + " money, "
                + " (case when spendtype in(" + FuncString.margeString(stype, ",") + ") then 1 else 2 end) type "
                + "from abhousekeeperdetail a where operator=@operator "
                + "and left(actionmonth, 4)=@year"
                + ") t group by STATISTICKEY, type";
        }
        else {
            return false;
        }
        List<Entity> ret = DaoFactory.c(sql).setParamByName("operator", this.getCurrentUser().getId()) //$NON-NLS-1$
                                    .setParamByName("year", year) //$NON-NLS-1$
                                    .listQuery();
        if(type.startsWith("MONTHSPLIT")) {
            List<Entity> temp = new ArrayList<Entity>();
            for(Iterator<Entity> iter = ret.iterator(); iter.hasNext();) {
                Entity e = iter.next();
                if(!"1".equals(e.getString("TYPE"))){
                    temp.add(e);
                    Date d = FuncDate.toDate("yyyy-MM", e.getString("MONTH"));
                    Calendar cal = Calendar.getInstance();
                    int curMonth = cal.get(Calendar.MONTH);
                    int curYear = cal.get(Calendar.YEAR);
                    cal.setTime(d);
                    int m = cal.get(Calendar.MONTH);
                    int y = cal.get(Calendar.YEAR);
                    int overDayInThisMonth = 1;
                    if(curYear == y && curMonth == m) {
                        cal.setTime(new Date());
                        overDayInThisMonth = cal.get(Calendar.DAY_OF_MONTH);
                    }
                    else {
                        cal.set(Calendar.MONTH, cal.get(Calendar.MONTH) + 1);
                        cal.set(Calendar.DATE, cal.get(Calendar.DATE) - 1);
                        overDayInThisMonth = cal.get(Calendar.DAY_OF_MONTH);
                    }
                    e.set("DAY", overDayInThisMonth);
                }
            }
            for(Iterator<Entity> iter = ret.iterator(); iter.hasNext();) {
                Entity e = iter.next();
                if("1".equals(e.getString("TYPE"))){
                    for(Iterator<Entity> iter2 = temp.iterator(); iter2.hasNext();) {
                        Entity e2 = iter2.next();
                        if(e2.getString("STATISTICKEY").equals(e.getString("STATISTICKEY"))) {
                            e2.set("MONEY2", e.getString("MONEY"));
                        }
                        else if(FuncString.isBlank(e2.getString("MONEY2"))) {
                            e2.set("MONEY2", "0");
                        }
                    }
                }
            }
            ret = temp;
            System.out.println(Entity.toJSON(ret, "statistic"));
        }
        this.setResult(Entity.toJSON(ret, "statistic")); //$NON-NLS-1$
        return true;
    }
    
    public boolean getYears() {
        String sql = "select distinct date_format(actiondate, '%Y') year from abhousekeeper2 where operator=?"; //$NON-NLS-1$
        this.setResult(Entity.toJSON(DaoFactory.c(sql).setParameter(this.getCurrentUser().getId()).listQuery(), "year")); //$NON-NLS-1$
        return true;
    }
    
    public boolean exportExcel() {
        if(!"1".equals(this.getSessionAttr("HouseKeeperDetail.exportExcel"))) {
            try {
                this.setSessionAttr("HouseKeeperDetail.exportExcel", "1");
                String sql= "select actionMonth, concat(spendtype,'-',spendname) spendtype,subspendname,money, date_format(actiondate, '%Y-%m-%d') actiondateStr from ABHouseKeeperDetail where actionMonth=? and operator=? order by spendtype"; //$NON-NLS-1$
                String month = this.getRequestParameter("month"); //$NON-NLS-1$
                if(FuncString.isBlank(month)) {
                    month = FuncDate.getCurrentYearMonth();
                }
                File file = new File(Mango.getProperty(Mango.TEMP) + File.separator + UUID.randomUUID() + ".txt");
                BufferedWriter writer = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(file)));
                Iterator<Entity> iter = DaoFactory.c(sql).setParameter(month, this.getUserId()).queryIterator();
                writer.write("月份|消费类型|消费子类|金额|消费日期\n");
                while(iter.hasNext()) {
                    StringBuilder sb = new StringBuilder();
                    Entity e = iter.next();
                    for(String fn : e.getFieldNames()) {
                        sb.append(e.get(fn)).append("|");
                    }
                    sb.delete(sb.length() - 1, sb.length());
                    writer.write(sb.toString());
                    writer.write("\n");
                }
                writer.flush();
                writer.close();
                this.getResponse().setContentType("application/x-download");
                this.getResponse().addHeader("Content-Disposition","attachment;filename="+ month + ".txt");
                this.getResponse().setContentLength((int)file.length());
                ServletOutputStream sos = this.getResponse().getOutputStream();
                FileInputStream fin = new FileInputStream(file);
                byte[] buf = new byte[1024];
                int len = -1;
                while ((len = fin.read(buf)) != -1) {
                    System.out.println("len :" + len);
                    sos.write(buf, 0, len);
                }
                fin.close();
                sos.flush();
                sos.close();
            }
            catch (Exception e) {
                e.printStackTrace();
            }
            finally {
                this.getSession().removeAttribute("HouseKeeperDetail.exportExcel");
            }
        }
        return true;
    }
}
